CREATE VIEW [dbo].[vCsCommitteeMember] AS
SELECT     
    Name.ID,
    Name.FULL_NAME AS FullName,
    Name.TITLE AS Title,
    Name.COMPANY AS Organization,
    Name.WORK_PHONE AS WorkPhone,
    Name.HOME_PHONE AS HomePhone,
    Name.FAX AS Fax,
    Name.TOLL_FREE AS TollFreePhone,
    Name.LAST_FIRST,
    Name.EMAIL AS Email,
    Name.EXCLUDE_DIRECTORY AS ExcludeDirectory,
    Name.STATUS AS [Status],
    NA_Bill.FULL_ADDRESS AS BillingAddress,
    NA_Mail.FULL_ADDRESS AS MailingAddress,
    Product.TITLE AS CommitteeName,
    Product.DESCRIPTION AS CommitteeDescription,
    Product.GROUP_3 AS CommitteeGroup,
    Committee_Position.TITLE AS Position,
    Committee_Position.RANK AS PositionRank,
    Product.NOTE AS Note,
    Activity.EFFECTIVE_DATE AS TermStart,
    Activity.THRU_DATE AS TermEnd,
    Activity.PRODUCT_CODE AS CommitteeProductCode
FROM    
    Activity INNER JOIN
            Name ON Activity.ID = Name.ID INNER JOIN
                Product ON Activity.PRODUCT_CODE = Product.PRODUCT_CODE INNER JOIN
                Name_Address NA_Mail ON Name.MAIL_ADDRESS_NUM = NA_Mail.ADDRESS_NUM INNER JOIN
                Name_Address NA_Bill ON Name.BILL_ADDRESS_NUM = NA_Bill.ADDRESS_NUM INNER JOIN
                Committee_Position ON Activity.ACTION_CODES = Committee_Position.POSITION_CODE
WHERE     
    (Product.PROD_TYPE = 'COMMITTEE')
    AND
    (Product.WEB_OPTION = 1)
    AND
    (Activity.EFFECTIVE_DATE <= GETDATE())
    AND
    (Activity.THRU_DATE IS NULL OR (Activity.THRU_DATE >= GETDATE()))

GO